﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection con_pharm;
        private SqlConnection con_countdw;
        private DataTable dt_rep;
        private DataTable dt_rep1;
        private delegate void Deleg();
        private Deleg del;
        private DataTable dt_pharmsens = new DataTable();
        private DataTable dt_pharm = new DataTable();
        private int newpharmcode = 0;
        private struct date_str { public DateTime dt; public string dtname; }
        private date_str[] dt_str = new date_str[] { };

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                dateTimePicker2.Value = DateTime.Today.AddDays(-1).Date;
                dateTimePicker1.Value = DateTime.Today.AddDays(-10).Date;

                con_pharm = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                con_countdw = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=CountDW;Integrated Security=True");

                dt_pharmsens.Load(PharmSensorSel(con_countdw));
                dataGridView1.DataSource = dt_pharmsens.DefaultView;
                dataGridView1.Sort(dataGridView1.Columns[2], ListSortDirection.Ascending);

                SqlCommand com = con_pharm.CreateCommand();
                com.CommandText = "pas_PharmSel";
                com.CommandType = CommandType.StoredProcedure;
                com.Parameters.AddWithValue("@fl", true);
                com.Parameters.AddWithValue("@onlyopen", false);
                if (con_pharm.State == ConnectionState.Closed) con_pharm.Open();
                dt_pharm.Load(com.ExecuteReader());
                dt_pharm.Columns.Remove(dt_pharm.Columns[2]);
                dt_pharm.Columns.Remove(dt_pharm.Columns[2]);
                dt_pharm.Columns[0].ColumnName = "Код аптеки";
                dt_pharm.Columns[1].ColumnName = "Аптека";

                SqlCommand getdate = con_pharm.CreateCommand();
                getdate.CommandText = @"pas_MonthSel";
                getdate.CommandType = CommandType.StoredProcedure;
                DataTable date = new DataTable();
                if (con_pharm.State == ConnectionState.Closed) con_pharm.Open();
                date.Load(getdate.ExecuteReader());
                if (date.Rows.Count > 0)
                {
                    foreach (DataRow dr in date.Rows)
                    {
                        Array.Resize(ref dt_str, dt_str.Length + 1);
                        dt_str[dt_str.Length - 1].dt = Convert.ToDateTime(dr[0]);
                        dt_str[dt_str.Length - 1].dtname = dr[1].ToString();
                        comboBox1.Items.Add(dr[1].ToString());
                        if (DateTime.Now.Date.Month == Convert.ToDateTime(dr[0]).Month)
                            comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
                    }

                }
                date = null;
                getdate = null;
                dataGridView2.ScrollBars = ScrollBars.Vertical;
                dataGridView3.ScrollBars = ScrollBars.Vertical;
                button4_Click(sender,e);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private SqlDataReader PharmSensorSel(SqlConnection conn)
        {
            try
            {
                SqlCommand comm = conn.CreateCommand();
                comm.CommandText = "pas_SensorPharmSel";
                comm.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed) conn.Open();

                return comm.ExecuteReader();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (con_pharm.State == ConnectionState.Closed) con_pharm.Open();
            try
            {

                dt_rep.Load(RepProc(con_pharm));

                con_pharm.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                con_pharm.Close();
            }

        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
            {   
                SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            }
        }

        public static bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        sw.Write(col.HeaderText + "\t");
                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        sw.Write(table[col_n[y], i].Value + "\t");
                    sw.Write(" \r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    Process newProc = Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex > -1)
            {
                textBox1.Text = dataGridView1[0, e.RowIndex].Value.ToString();
                textBox2.Text = dataGridView1[1, e.RowIndex].Value.ToString();
                textBox3.Text = dataGridView1[2, e.RowIndex].Value.ToString();
                textBox4.Text = dataGridView1[3, e.RowIndex].Value.ToString();
                if (dataGridView1[4, e.RowIndex].Value == DBNull.Value) newpharmcode = 0;
                else newpharmcode = Convert.ToInt32(dataGridView1[4, e.RowIndex].Value);
            }
        }

        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {
            if (dataGridView1.DataSource != null)
            {
                dataGridView1.Columns[2].Width *= 2;
                dataGridView1.Columns[3].Width *= 2;
                dataGridView1.Sort(dataGridView1.Columns[2], ListSortDirection.Ascending);
            }
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                if (e.RowIndex > -1)
                {
                    if (MessageBox.Show("База - " + dataGridView1[0, e.RowIndex].Value.ToString() + "\n" +
                        "Счетчик - " + dataGridView1[1, e.RowIndex].Value.ToString() + "\n" +
                        "Аптека в базе счетчика - " + dataGridView1[2, e.RowIndex].Value.ToString() + "\n" +
                        "Аптека МАП - " + dataGridView1[3, e.RowIndex].Value.ToString()
                        , "Удалить связь?", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        SqlCommand comm = con_countdw.CreateCommand();
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.CommandText = "pas_SensorPharmInsDel";
                        comm.Parameters.AddWithValue("@pharmcode", dataGridView1[4, e.RowIndex].Value);
                        comm.Parameters.AddWithValue("@sens_id", dataGridView1[1, e.RowIndex].Value);
                        comm.Parameters.AddWithValue("@base", dataGridView1[0, e.RowIndex].Value);
                        comm.Parameters.AddWithValue("@del", true);
                        comm.ExecuteNonQuery();

                        MessageBox.Show("База - " + dataGridView1[0, e.RowIndex].Value.ToString() + "\n" +
                        "Счетчик - " + dataGridView1[1, e.RowIndex].Value.ToString() + "\n" +
                        "Аптека в базе счетчика - " + dataGridView1[2, e.RowIndex].Value.ToString() + "\n" +
                        "Аптека МАП - " + dataGridView1[3, e.RowIndex].Value.ToString()
                        , "Связь удалена");

                        dataGridView1.DataSource = null;
                        dt_pharmsens = null;
                        dt_pharmsens = new DataTable();
                        dt_pharmsens.Load(PharmSensorSel(con_countdw));
                        dataGridView1.DataSource = dt_pharmsens.DefaultView;
                        toolStripStatusLabel1.Text = "Всего строк: " + dataGridView1.RowCount.ToString();
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (newpharmcode != 0)
                {
                    if (MessageBox.Show("База - " + textBox1.Text + "\n" +
                            "Счетчик - " + textBox2.Text + "\n" +
                            "Аптека в базе счетчика - " + textBox3.Text + "\n" +
                            "Аптека МАП - " + textBox4.Text
                            , "Добавить связь?", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        SqlCommand comm = con_countdw.CreateCommand();
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.CommandText = "pas_SensorPharmInsDel";
                        comm.Parameters.AddWithValue("@pharmcode", newpharmcode);
                        comm.Parameters.AddWithValue("@sens_id", Convert.ToInt32(textBox2.Text));
                        comm.Parameters.AddWithValue("@base", textBox1.Text);
                        comm.Parameters.AddWithValue("@del", false);
                        comm.ExecuteNonQuery();

                        MessageBox.Show("База - " + textBox1.Text + "\n" +
                            "Счетчик - " + textBox2.Text + "\n" +
                            "Аптека в базе счетчика - " + textBox3.Text + "\n" +
                            "Аптека МАП - " + textBox4.Text
                            , "Связь добавлена");

                        dataGridView1.DataSource = null;
                        dt_pharmsens = null;
                        dt_pharmsens = new DataTable();
                        dt_pharmsens.Load(PharmSensorSel(con_countdw));
                        dataGridView1.DataSource = dt_pharmsens.DefaultView;
                        toolStripStatusLabel1.Text = "Всего строк: " + dataGridView1.RowCount.ToString();
                    }
                }
                else
                {
                    MessageBox.Show("Выберите аптеку");
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void button2_Click(object sender, EventArgs e)
        {
            Form2 f2 = new Form2();
            f2.dt = dt_pharm;
            if (f2.ShowDialog() == DialogResult.OK)
            {
                newpharmcode = f2.pharmcode;
                textBox4.Text = f2.pharmname;
            }
            f2.Close();
            f2 = null;
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void textBox5_TextChanged(object sender, EventArgs e)
        {
            HideShowRows(textBox5,dataGridView1);
        }

        private void textBox5_Enter(object sender, EventArgs e)
        {
            HideShowRows(textBox5, dataGridView1);
        }

        private void HideShowRows(TextBox tb,DataGridView dgv)
        {
            CurrencyManager cm = (CurrencyManager)dgv.BindingContext[dgv.DataSource];
            cm.SuspendBinding();
            foreach (DataGridViewRow r in dgv.Rows)
            {
                r.Visible = true;
                if (!Regex.Match(Convert.ToString(dgv[dgv.SortedColumn.Name, r.Index].Value), tb.Text, RegexOptions.IgnoreCase).Success)
                    r.Visible = false;
            }
            cm.ResumeBinding();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand proc = con_countdw.CreateCommand();
                proc.CommandType = CommandType.StoredProcedure;
                proc.CommandTimeout = 600;
                proc.CommandText = "pas_DownloadData";
                for (int i = 0; i < comboBox1.Items.Count; i++)
                {
                    if (comboBox1.Text == dt_str[i].dtname) proc.Parameters.AddWithValue("@month", dt_str[i].dt);
                }
                if (con_countdw.State == ConnectionState.Closed) con_countdw.Open();
                proc.ExecuteNonQuery();
                MessageBox.Show("Данные за " + comboBox1.Text + " загружены");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            try
            {
                SqlCommand proc = con_countdw.CreateCommand();
                proc.CommandType = CommandType.StoredProcedure;
                proc.CommandTimeout = 600;
                proc.CommandText = "pas_SensorControl";
                proc.Parameters.AddWithValue("@datebeg", dateTimePicker1.Value.Date);
                proc.Parameters.AddWithValue("@dateend", dateTimePicker2.Value.Date);
                if (con_countdw.State == ConnectionState.Closed) con_countdw.Open();
                hScrollBar1.Maximum = hScrollBar1.Minimum;
                dataGridView2.DataSource = null;
                dataGridView3.DataSource = null;
                dt_rep = null;
                dt_rep = new DataTable();
                dt_rep.Load(proc.ExecuteReader());

                dt_rep1 = null;
                dt_rep1 = new DataTable();
                DataRow[] foundRows = dt_rep.Select("[Код аптеки] < 1");
                for(int i = 0; i < dt_rep.Columns.Count; i++)
                {
                    if (i > 3 && (i & 1) == 0) dt_rep1.Columns.Add(dt_rep.Columns[i].ColumnName, typeof(Double));
                    else dt_rep1.Columns.Add(dt_rep.Columns[i].ColumnName);
                }
                
                foreach (DataRow dr in foundRows)
                {
                    object[] row = new object[] { };
                    foreach (object ob in dr.ItemArray)
                    {
                        Array.Resize(ref row, row.Length + 1);
                        row[row.Length - 1] = ob;
                    }
                    dt_rep1.Rows.Add(row);
                }
                for (int i = 0; i < dt_rep.Rows.Count; i++)
                {
                    if (Convert.ToInt32(dt_rep.Rows[i][0]) < 1) dt_rep.Rows[i].Delete();
                }

                dataGridView2.DataSource = dt_rep.DefaultView;
                dataGridView3.DataSource = dt_rep1.DefaultView;

                foreach (DataGridViewColumn col in dataGridView2.Columns)
                {
                    if (col.Index <= 3) col.Frozen = true;
                    else
                        if ((col.Index & 1) == 1) col.Visible = false;
                        else col.DefaultCellStyle.Format = "p";
                }
                foreach (DataGridViewColumn col in dataGridView3.Columns)
                {
                    if (col.Index <= 3) col.Frozen = true;
                    else
                        if ((col.Index & 1) == 1) col.Visible = false;
                        //else col.DefaultCellStyle.Format = "p";
                }
                foreach (DataGridViewRow row in dataGridView3.Rows)
                {
                    if (Convert.ToInt32(row.Cells[0].Value) == -3)
                        for (int i = 4; i < row.Cells.Count; i += 2)
                        {
                            row.Cells[i].Style.Format = "p";
                        }
                }
                dataGridView3.Columns[0].HeaderText = "Ид";
                dataGridView3.Columns[1].HeaderText = "Параметр";
                dataGridView3.Columns[2].HeaderText = " ";
                dataGridView3.Columns[3].HeaderText = "Итог за период";
                //dataGridView3.Columns[2].Visible = false;
                //dataGridView3.Columns[1].Width += dataGridView3.Columns[2].Width;
                dataGridView2.Sort(dataGridView2.Columns[1], ListSortDirection.Ascending);
                dataGridView3.Sort(dataGridView3.Columns[1], ListSortDirection.Ascending);
                toolStripStatusLabel1.Text = "Всего строк: " + dataGridView2.RowCount.ToString();
                hScrollBar1.Maximum = (dataGridView3.ColumnCount - 4) / 2 + 1;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (tabControl1.SelectedIndex)
            {
                case 1:
                    toolStripStatusLabel1.Text = "Всего строк: " + dataGridView1.RowCount.ToString();
                    break;
                case 0:
                    toolStripStatusLabel1.Text = "Всего строк: " + dataGridView2.RowCount.ToString();
                    break;
            }
        }

        private void dataGridView2_DataSourceChanged(object sender, EventArgs e)
        {
            //if (dataGridView2.DataSource != null)
            //    dataGridView2 = DataGridViewColor(dataGridView2);
            
        }

        private DataGridView DataGridViewColor(DataGridView dgv)
        {
            for(int i = 4; i < dgv.ColumnCount;i++ )
            {
                if ((i & 1) == 0)
                {
                    for (int j = 0; j < dgv.RowCount; j++)
                    {
                        if (dgv[i, j].Value == DBNull.Value)
                            dgv[i, j].Style.BackColor = Color.BlueViolet;
                        else
                            if (Convert.ToDouble(dgv[i, j].Value) < 0)
                                dgv[i, j].Style.BackColor = Color.Red;
                            else
                                if (Convert.ToDouble(dgv[i, j].Value) > 0.1)
                                    dgv[i, j].Style.BackColor = Color.Yellow;
                    }
                }
            }
            
            return dgv;
        }

        private void dataGridView2_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                Form3 f3 = new Form3();
                f3.text = dataGridView2[e.ColumnIndex + 1, e.RowIndex].Value.ToString();
                string comment = f3.text;
                if (f3.ShowDialog() == DialogResult.OK)
                {
                    if (f3.text != comment)
                    {
                        SqlCommand proc = con_countdw.CreateCommand();
                        proc.CommandText = "pas_CommentInsUpd";
                        proc.CommandType = CommandType.StoredProcedure;
                        proc.Parameters.AddWithValue("@date", Convert.ToDateTime(dataGridView2.Columns[e.ColumnIndex].Name));
                        proc.Parameters.AddWithValue("@pharmcode", dataGridView2[0, e.RowIndex].Value);
                        if (f3.text == "") proc.Parameters.AddWithValue("@comm", null);
                        else proc.Parameters.AddWithValue("@comm", f3.text);
                        if (con_countdw.State == ConnectionState.Closed) con_countdw.Open();
                        proc.ExecuteNonQuery();
                        if (f3.text == "") dataGridView2[e.ColumnIndex + 1, e.RowIndex].Value = DBNull.Value;
                        else dataGridView2[e.ColumnIndex + 1, e.RowIndex].Value = f3.text;
                    }
                }
                f3.Close();
                f3 = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView2_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            //foreach (DataGridViewColumn col in dataGridView2.Columns)
            //{
            //    if (col.Index > 3 && (col.Index & 1) == 0)
            //    {
            //        int i = col.Index;
            //        int j = e.RowIndex;
            //        if (dataGridView2[i, j].Value == DBNull.Value)
            //            dataGridView2[i, j].Style.BackColor = Color.BlueViolet;
            //        else
            //            if (Convert.ToDouble(dataGridView2[i, j].Value) < 0)
            //                dataGridView2[i, j].Style.BackColor = Color.Red;
            //            else
            //                if (Convert.ToDouble(dataGridView2[i, j].Value) > 0.1)
            //                    dataGridView2[i, j].Style.BackColor = Color.Yellow;
            //    }
            //}
            
        }

        private void dataGridView2_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (dataGridView2.DataSource != null)
            {
                if (e.RowIndex > -1)
                    if (e.ColumnIndex > 3 && (e.ColumnIndex & 1) == 0)
                    {
                        if (dataGridView2[e.ColumnIndex + 1, e.RowIndex].Value != DBNull.Value)
                        {
                            Rectangle newRect = new Rectangle(e.CellBounds.X + 1, e.CellBounds.Y + 1, e.CellBounds.Width - 3, e.CellBounds.Height - 3);
                            Color color = Color.Lime;
                            float w = 2;
                            e.Paint(newRect, DataGridViewPaintParts.All & ~DataGridViewPaintParts.Border);
                            using (Pen p = new Pen(color, w))
                            {
                                e.Graphics.DrawRectangle(p, newRect);
                            }
                            e.Handled = true;
                        }
                    }
            }
        }

        private void dataGridView2_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            //int i = 0, j = 0;
            //if (e.ColumnIndex > 3 && (e.ColumnIndex & 1) == 0 && e.RowIndex > -1)
            //{
            //    i = e.ColumnIndex;
            //    j = e.RowIndex;
            //    if (dataGridView2[i, j].Value == DBNull.Value)
            //        dataGridView2[i, j].Style.BackColor = Color.BlueViolet;
            //    else
            //        if (Convert.ToDouble(dataGridView2[i, j].Value) < 0)
            //            dataGridView2[i, j].Style.BackColor = Color.Red;
            //        else
            //            if (Convert.ToDouble(dataGridView2[i, j].Value) > 0.1)
            //                dataGridView2[i, j].Style.BackColor = Color.Yellow;
            //}
        }

        private void button5_Click(object sender, EventArgs e)
        {
            CreateExcel("", dataGridView2, dataGridView3);
        }

        private string CreateExcel(string filename, DataGridView dgv,DataGridView dgv1)
        {
            
            if (dgv.DataSource != null)
            {
                int r = 0;
                int c = 0;
                string fname = dateTimePicker1.Value.Date.ToShortDateString() + " - " + dateTimePicker2.Value.Date.ToShortDateString()+ ".xlsx";
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Hashtable myHashtable = new Hashtable();
                myHashtable = CheckExcellProcesses(myHashtable);

                Excel.Application exapp = new Excel.Application();
                Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];
                exapp.DisplayAlerts = false;
                try
                {
                    if (filename == "")
                    {
                        FileDialog sfd = new SaveFileDialog();
                        sfd.FileName = fname;
                        sfd.Filter = "Excel | *.xlsx";
                        if (sfd.ShowDialog() == DialogResult.OK)filename = sfd.FileName;
                        sfd = null;
                    }
                    if (Path.GetExtension(filename) == "xlsx") Path.ChangeExtension(filename, "xlsx");
                    if (filename != "")
                    {
                        int a = 0;
                        foreach (DataGridViewColumn col in dgv.Columns)
                        {
                            if (col.Index < 4)
                            {
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Value2 = col.Name;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                            }
                            if (col.Index > 3 && (col.Index & 1) == 0)
                            {
                                exws.get_Range(exws.Cells[1, col.Index - a + 1], exws.Cells[1, col.Index - a + 1]).Value2 = col.Name;
                                exws.get_Range(exws.Cells[1, col.Index - a + 1], exws.Cells[1, col.Index - a + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[1, col.Index - a + 1], exws.Cells[1, col.Index - a + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                                a++;

                            }
                        }
                        int r_ind = 2;
                        foreach (DataGridViewRow row in dgv.Rows)
                        {
                            a = 0;
                            foreach (DataGridViewCell cell in row.Cells)
                            {
                                c = cell.ColumnIndex;
                                r = cell.RowIndex;
                                if (cell.ColumnIndex < 4)
                                {
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).Value2 = cell.Value;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                    if (cell.ColumnIndex == 2)
                                        exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).EntireColumn.NumberFormat = "DD.MM.YYYY";
                                }
                                if (cell.ColumnIndex > 3 && (cell.ColumnIndex & 1) == 0)
                                {
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Value2 = cell.Value;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Cells.Interior.Color = SetColor(cell.Value);
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).NumberFormat = "##0.00%";
                                    if (dgv[cell.ColumnIndex + 1, cell.RowIndex].Value != DBNull.Value)
                                        exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).AddComment(dgv[cell.ColumnIndex + 1, cell.RowIndex].Value.ToString());
                                    a++;
                                }
                            }
                            r_ind++;
                        }
                        foreach (DataGridViewRow row in dgv1.Rows)
                        {
                            a = 0;
                            foreach (DataGridViewCell cell in row.Cells)
                            {
                                c = cell.ColumnIndex;
                                r = cell.RowIndex;
                                if (cell.ColumnIndex < 4)
                                {
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).Value2 = cell.Value;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex + 1], exws.Cells[r_ind, cell.ColumnIndex + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSalmon;
                                }
                                if (cell.ColumnIndex > 3 && (cell.ColumnIndex & 1) == 0)
                                {
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Value2 = Convert.ToDouble(cell.Value);
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSalmon;
                                    exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                    if (Convert.ToInt32(dgv1[0, cell.RowIndex].Value) == -3)
                                    {
                                        exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).Value2 = cell.Value.ToString().Replace(",",".");
                                        exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).NumberFormat = "##0.00%";
                                    }
                                    else exws.get_Range(exws.Cells[r_ind, cell.ColumnIndex - a + 1], exws.Cells[r_ind, cell.ColumnIndex - a + 1]).NumberFormat = "##0";
                                    a++;
                                }
                            }
                            r_ind++;
                        }
                        exws.Application.ActiveWindow.SplitRow = 1;
                        exws.Application.ActiveWindow.SplitColumn = 1;
                        exws.Application.ActiveWindow.SplitColumn = 2;
                        exws.Application.ActiveWindow.SplitColumn = 3;
                        exws.Application.ActiveWindow.SplitColumn = 4;
                        exws.Application.ActiveWindow.FreezePanes = true;
                        exws.get_Range(exws.Cells[1, 1], exws.Cells[r_ind - 1, a]).EntireColumn.AutoFit();
                        exwb.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                        exapp.Quit();
                        KillExcel(myHashtable);
                        MessageBox.Show("Файл сохранен");
                    }
                }
                catch (Exception ex)
                {
                    exapp.Quit();
                    KillExcel(myHashtable);
                    MessageBox.Show(ex.Message + " c " +  c.ToString() + " r " + r.ToString() + "\n" + dataGridView2[c,r].Value.ToString() );
                }
                
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
            return filename;
        }

        private Excel.XlRgbColor SetColor(object value)
        {
            Excel.XlRgbColor color;

            if (value == DBNull.Value) color = Excel.XlRgbColor.rgbBlueViolet;
            else
            {
                double val = Convert.ToDouble(value);
                if (val < 0) color = Excel.XlRgbColor.rgbRed;
                else
                    if (val < 0.1) color = Excel.XlRgbColor.rgbWhite;
                    else color = Excel.XlRgbColor.rgbYellow;
            }

            return color;
        }
        private void dataGridView2_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {
            foreach (DataGridViewColumn col in dataGridView2.Columns)
            {
                if (col.Index > 3 && (col.Index & 1) == 0)
                {
                    int i = col.Index;
                    int j = e.RowIndex;
                    if (dataGridView2[i, j].Value == DBNull.Value)
                        dataGridView2[i, j].Style.BackColor = Color.BlueViolet;
                    else
                        if (Convert.ToDouble(dataGridView2[i, j].Value) < 0)
                            dataGridView2[i, j].Style.BackColor = Color.Red;
                        else
                            if (Convert.ToDouble(dataGridView2[i, j].Value) > 0.1)
                                dataGridView2[i, j].Style.BackColor = Color.Yellow;
                }
            }
        }

        private void dataGridView2_ColumnSortModeChanged(object sender, DataGridViewColumnEventArgs e)
        {
            //if (dataGridView2.DataSource != null)
            //    dataGridView2 = DataGridViewColor(dataGridView2);
        }

        private void dataGridView3_RowPrePaint(object sender, DataGridViewRowPrePaintEventArgs e)
        {   
            //if (Convert.ToInt32(dataGridView3[0,e.RowIndex].Value) == -3)
            //    for (int i = 4; i < dataGridView3.ColumnCount; i += 2)
            //        dataGridView3[i, e.RowIndex].Style.Format = "p";
            
        }

        private void dataGridView3_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            
        }

        private void dataGridView3_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex > -1 && e.ColumnIndex > -1)
                if (Convert.ToInt32(dataGridView3[0, e.RowIndex].Value) == -3)
                    if (e.ColumnIndex > 3 && (e.ColumnIndex & 1) == 0)
                    {
                        dataGridView3[e.ColumnIndex, e.RowIndex].Style.Format = "p";
                        //e.Handled = true;
                    }        
        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        private void dataGridView2_Scroll(object sender, ScrollEventArgs e)
        {
            
        }

        private void dataGridView3_Scroll(object sender, ScrollEventArgs e)
        {
                //dataGridView2.FirstDisplayedScrollingColumnIndex = dataGridView3.FirstDisplayedScrollingColumnIndex;
        }

        private void hScrollBar1_Scroll(object sender, ScrollEventArgs e)
        {
            dataGridView3.FirstDisplayedScrollingColumnIndex = 4 + e.NewValue * 2;
            dataGridView2.FirstDisplayedScrollingColumnIndex = 4 + e.NewValue * 2;
        }

        private void dataGridView2_CurrentCellChanged(object sender, EventArgs e)
        {
            if (dataGridView2.DataSource != null && dataGridView3.DataSource != null && dataGridView2.CurrentCell != null)
            {
                dataGridView3.CurrentCell = dataGridView3[dataGridView2.CurrentCell.ColumnIndex, dataGridView3.CurrentRow.Index];
                if (dataGridView2.CurrentCell.ColumnIndex > 3) hScrollBar1.Value = (dataGridView2.FirstDisplayedScrollingColumnIndex - 4) / 2;
                if (dataGridView2.CurrentCell.ColumnIndex == dataGridView2.ColumnCount - 2) hScrollBar1.Value = hScrollBar1.Maximum;
            }
        }

        private void dataGridView3_CurrentCellChanged(object sender, EventArgs e)
        {
            if (dataGridView2.DataSource != null && dataGridView3.DataSource != null && dataGridView3.CurrentCell != null)
            {
                dataGridView2.CurrentCell = dataGridView2[dataGridView3.CurrentCell.ColumnIndex, dataGridView2.CurrentRow.Index];
                if(dataGridView3.CurrentCell.ColumnIndex > 3) hScrollBar1.Value = (dataGridView3.FirstDisplayedScrollingColumnIndex - 4) / 2;
                if (dataGridView3.CurrentCell.ColumnIndex == dataGridView3.ColumnCount - 2) hScrollBar1.Value = hScrollBar1.Maximum;
            }
        }

        private void ChildForm_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Control && e.KeyCode == Keys.F)
            {
                if (tabControl1.SelectedTab == tabPage2) textBox6.Focus();
                if (tabControl1.SelectedTab == tabPage1) textBox5.Focus();

            }
           // else MessageBox.Show(e.KeyCode.ToString() + "\n" + e.Control.ToString());
        }

        private void textBox6_TextChanged(object sender, EventArgs e)
        {
            HideShowRows(textBox6, dataGridView2);
        }


    }
}
